using System;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Utility;
using OgilvyOne.IDAL;
using OgilvyOne.Model;

namespace OgilvyOne.SqlServerDAL
{
    public class Message : IMessage
    {
        // Fields
        private const string SQL_Message_Info_GETLIST = "select Message_ID,Message_Type,Title,Message_Desc,Staff_ID,Create_Date,IsDel from Message_Info where Message_Type = @Message_Type and IsDel = '0'";
        private const string SQL_Message_Info_SAVE = "\r\n\t\tINSERT INTO [dbo].[Message_Info] (\r\n        [Message_Type],\r\n\t\t[Title],\r\n\t\t[Message_Desc],\r\n\t\t[Staff_ID],\r\n\t\t[Create_Date],\r\n\t\t[IsDel]\r\n\t\t) VALUES (\r\n            @Message_Type,\r\n\t\t\t@Title,\r\n\t\t\t@Message_Desc,\r\n\t\t\t@Staff_ID,\r\n\t\t\t@Create_Date,\r\n\t\t\t@IsDel\r\n\t\t)\r\n\t\t";

        // Methods
        public void DeleteMessage(int mid)
        {
            SqlConnection conn = null;
            string cmdText = string.Format("update Message_Info set isdel = '1' where  Message_id = {0}", mid);
            try
            {
                try
                {
                    conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    SQLHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, new SqlParameter[0]);
                }
                catch (Exception exception)
                {
                    throw new Exception("Message", exception);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public MessageInfo GetMessageInfo(int Message_id)
        {
            string cmdText = "select Message_ID,Title,Message_Desc,Staff_ID,Create_Date,IsDel,Message_Type from Message_Info where Message_ID = @Message_ID ";
            MessageInfo info = new MessageInfo();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Message_ID", SqlDbType.Int) };
            cmdParms[0].Value = Message_id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = this.LoadFromDataReader(reader);
                }
            }
            return info;
        }

        public IList GetMessageInfoList(int type)
        {
            IList list = new ArrayList();
            SqlDataReader reader = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Message_Type", SqlDbType.Int) };
            cmdParms[0].Value = type;
            try
            {
                try
                {
                    using (reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "select Message_ID,Message_Type,Title,Message_Desc,Staff_ID,Create_Date,IsDel from Message_Info where Message_Type = @Message_Type and IsDel = '0'", cmdParms))
                    {
                        while (reader.Read())
                        {
                            MessageInfo info = new MessageInfo();
                            info.Message_id = reader.GetInt32(0);
                            info.Message_Type = reader.GetInt32(1);
                            info.Title = reader.GetString(2);
                            info.Message_desc = reader.GetString(3);
                            info.Staff_id = reader.GetInt32(4);
                            list.Add(info);
                        }
                    }
                    return list;
                }
                catch (Exception exception)
                {
                    throw new Exception("Message", exception);
                }
            }
            finally
            {
                if (!((reader == null) || reader.IsClosed))
                {
                    reader.Close();
                }
            }
            return list;
        }

        public int InsertMessageInfo(MessageInfo mMessageInfo)
        {
            SqlConnection conn = null;
            int num = 0;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Message_Type", SqlDbType.Int), new SqlParameter("@Title", SqlDbType.VarChar, 200), new SqlParameter("@Message_Desc", SqlDbType.VarChar, 0xfa0), new SqlParameter("@Staff_ID", SqlDbType.Int), new SqlParameter("@Create_Date", SqlDbType.DateTime), new SqlParameter("@IsDel", SqlDbType.Char, 1), new SqlParameter("@RETURN_VALUE", SqlDbType.Int) };
            cmdParms[0].Value = mMessageInfo.Message_Type;
            cmdParms[1].Value = mMessageInfo.Title;
            cmdParms[2].Value = mMessageInfo.Message_desc;
            cmdParms[3].Value = mMessageInfo.Staff_id;
            cmdParms[4].Value = mMessageInfo.Create_date;
            cmdParms[5].Value = mMessageInfo.Isdel;
            cmdParms[6].Direction = ParameterDirection.Output;
            try
            {
                try
                {
                    conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    SQLHelper.ExecuteNonQuery(conn, CommandType.Text, "\r\n\t\tINSERT INTO [dbo].[Message_Info] (\r\n        [Message_Type],\r\n\t\t[Title],\r\n\t\t[Message_Desc],\r\n\t\t[Staff_ID],\r\n\t\t[Create_Date],\r\n\t\t[IsDel]\r\n\t\t) VALUES (\r\n            @Message_Type,\r\n\t\t\t@Title,\r\n\t\t\t@Message_Desc,\r\n\t\t\t@Staff_ID,\r\n\t\t\t@Create_Date,\r\n\t\t\t@IsDel\r\n\t\t)\r\n\t\t", cmdParms);
                    num = (int)cmdParms[6].Value;
                }
                catch (Exception exception)
                {
                    throw new Exception("Message", exception);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return num;
        }

        private MessageInfo LoadFromDataReader(SqlDataReader reader)
        {
            MessageInfo info = new MessageInfo();
            if (!reader.IsDBNull(0))
            {
                info.Message_id = reader.GetInt32(0);
            }
            if (!reader.IsDBNull(1))
            {
                info.Title = reader.GetString(1);
            }
            if (!reader.IsDBNull(2))
            {
                info.Message_desc = reader.GetString(2);
            }
            if (!reader.IsDBNull(3))
            {
                info.Staff_id = reader.GetInt32(3);
            }
            if (!reader.IsDBNull(4))
            {
                info.Create_date = reader.GetDateTime(4);
            }
            if (!reader.IsDBNull(5))
            {
                info.Isdel = reader.GetString(5);
            }
            if (!reader.IsDBNull(6))
            {
                info.Message_Type = reader.GetInt32(6);
            }
            return info;
        }
    }
}